/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ru.ibrae.mchs.radiation.entities.controllers;

import ru.ibrae.mchs.radiation.common.PokazatelMap;
import ru.ibrae.mchs.radiation.entities.Result;
import ru.ibrae.mchs.radiation.entities.controllers.exceptions.NonexistentEntityException;
import ru.ibrae.mchs.radiation.entities.controllers.exceptions.PreexistingEntityException;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.EntityNotFoundException;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import ru.ibrae.mchs.radiation.entities.DicUnit;
import ru.ibrae.mchs.radiation.entities.Pokazatel;
import ru.ibrae.mchs.radiation.entities.DicTime;
import ru.ibrae.mchs.radiation.entities.Selsovet;
import ru.ibrae.mchs.radiation.entities.RajonOld;
import ru.ibrae.mchs.radiation.entities.SubjectRf;
import ru.ibrae.mchs.radiation.entities.GSPos;
import ru.ibrae.mchs.radiation.entities.Rajon;
import ru.ibrae.mchs.radiation.entities.DicIst;
import ru.ibrae.mchs.radiation.entities.Geoobject;
import java.util.ArrayList;

/**
 *
 * @author murr
 */
public class ResultJpaController {

    public ResultJpaController() {
        emf = Persistence.createEntityManagerFactory("DesktopApplication4PU");
    }
    private EntityManagerFactory emf = null;

    public EntityManager getEntityManager() {
        return emf.createEntityManager();
    }

    public void create(Result result) throws PreexistingEntityException, Exception {
        EntityManager em = null;
        try {
            em = getEntityManager();
            em.getTransaction().begin();
            DicUnit dicUnit = result.getDicUnit();
            if (dicUnit != null) {
                dicUnit = em.getReference(dicUnit.getClass(), dicUnit.getIdUnit());
                result.setDicUnit(dicUnit);
            }
            Pokazatel pokazatel = result.getPokazatel();
            if (pokazatel != null) {
                pokazatel = em.getReference(pokazatel.getClass(), pokazatel.getIdP());
                result.setPokazatel(pokazatel);
            }
            DicTime dicTime = result.getDicTime();
            if (dicTime != null) {
                dicTime = em.getReference(dicTime.getClass(), dicTime.getIdTime());
                result.setDicTime(dicTime);
            }
            Selsovet selsovet = result.getSelsovet();
            if (selsovet != null) {
                selsovet = em.getReference(selsovet.getClass(), selsovet.getIdSelsov());
                result.setSelsovet(selsovet);
            }
            RajonOld rajonOld = result.getRajonOld();
            if (rajonOld != null) {
                rajonOld = em.getReference(rajonOld.getClass(), rajonOld.getIdRajonO());
                result.setRajonOld(rajonOld);
            }
            SubjectRf subjectRf = result.getSubjectRf();
            if (subjectRf != null) {
                subjectRf = em.getReference(subjectRf.getClass(), subjectRf.getId());
                result.setSubjectRf(subjectRf);
            }
            GSPos GSPos = result.getGSPos();
            if (GSPos != null) {
                GSPos = em.getReference(GSPos.getClass(), GSPos.getIdPoselen());
                result.setGSPos(GSPos);
            }
            Rajon rajon = result.getRajon();
            if (rajon != null) {
                rajon = em.getReference(rajon.getClass(), rajon.getIdRajon());
                result.setRajon(rajon);
            }
            DicIst dicIst = result.getDicIst();
            if (dicIst != null) {
                dicIst = em.getReference(dicIst.getClass(), dicIst.getIdIst());
                result.setDicIst(dicIst);
            }
            Geoobject geoobject = result.getGeoobject();
            if (geoobject != null) {
                geoobject = em.getReference(geoobject.getClass(), geoobject.getIdNp());
                result.setGeoobject(geoobject);
            }
            em.persist(result);
            if (dicUnit != null) {
                dicUnit.getResultSet().add(result);
                dicUnit = em.merge(dicUnit);
            }
            if (pokazatel != null) {
                pokazatel.getResultSet().add(result);
                pokazatel = em.merge(pokazatel);
            }
            if (dicTime != null) {
                dicTime.getResultSet().add(result);
                dicTime = em.merge(dicTime);
            }
            if (selsovet != null) {
                selsovet.getResultSet().add(result);
                selsovet = em.merge(selsovet);
            }
            if (rajonOld != null) {
                rajonOld.getResultSet().add(result);
                rajonOld = em.merge(rajonOld);
            }
            if (subjectRf != null) {
                subjectRf.getResultSet().add(result);
                subjectRf = em.merge(subjectRf);
            }
            if (GSPos != null) {
                GSPos.getResultSet().add(result);
                GSPos = em.merge(GSPos);
            }
            if (rajon != null) {
                rajon.getResultSet().add(result);
                rajon = em.merge(rajon);
            }
            if (dicIst != null) {
                dicIst.getResultSet().add(result);
                dicIst = em.merge(dicIst);
            }
            if (geoobject != null) {
                geoobject.getResultSet().add(result);
                geoobject = em.merge(geoobject);
            }
            em.getTransaction().commit();
        } catch (Exception ex) {
            if (findResult(result.getIdRezult()) != null) {
                throw new PreexistingEntityException("Result " + result + " already exists.", ex);
            }
            throw ex;
        } finally {
            if (em != null) {
                em.close();
            }
        }
    }

    public void edit(Result result) throws NonexistentEntityException, Exception {
        EntityManager em = null;
        try {
            em = getEntityManager();
            em.getTransaction().begin();
            Result persistentResult = em.find(Result.class, result.getIdRezult());
            DicUnit dicUnitOld = persistentResult.getDicUnit();
            DicUnit dicUnitNew = result.getDicUnit();
            Pokazatel pokazatelOld = persistentResult.getPokazatel();
            Pokazatel pokazatelNew = result.getPokazatel();
            DicTime dicTimeOld = persistentResult.getDicTime();
            DicTime dicTimeNew = result.getDicTime();
            Selsovet selsovetOld = persistentResult.getSelsovet();
            Selsovet selsovetNew = result.getSelsovet();
            RajonOld rajonOldOld = persistentResult.getRajonOld();
            RajonOld rajonOldNew = result.getRajonOld();
            SubjectRf subjectRfOld = persistentResult.getSubjectRf();
            SubjectRf subjectRfNew = result.getSubjectRf();
            GSPos GSPosOld = persistentResult.getGSPos();
            GSPos GSPosNew = result.getGSPos();
            Rajon rajonOld = persistentResult.getRajon();
            Rajon rajonNew = result.getRajon();
            DicIst dicIstOld = persistentResult.getDicIst();
            DicIst dicIstNew = result.getDicIst();
            Geoobject geoobjectOld = persistentResult.getGeoobject();
            Geoobject geoobjectNew = result.getGeoobject();
            if (dicUnitNew != null) {
                dicUnitNew = em.getReference(dicUnitNew.getClass(), dicUnitNew.getIdUnit());
                result.setDicUnit(dicUnitNew);
            }
            if (pokazatelNew != null) {
                pokazatelNew = em.getReference(pokazatelNew.getClass(), pokazatelNew.getIdP());
                result.setPokazatel(pokazatelNew);
            }
            if (dicTimeNew != null) {
                dicTimeNew = em.getReference(dicTimeNew.getClass(), dicTimeNew.getIdTime());
                result.setDicTime(dicTimeNew);
            }
            if (selsovetNew != null) {
                selsovetNew = em.getReference(selsovetNew.getClass(), selsovetNew.getIdSelsov());
                result.setSelsovet(selsovetNew);
            }
            if (rajonOldNew != null) {
                rajonOldNew = em.getReference(rajonOldNew.getClass(), rajonOldNew.getIdRajonO());
                result.setRajonOld(rajonOldNew);
            }
            if (subjectRfNew != null) {
                subjectRfNew = em.getReference(subjectRfNew.getClass(), subjectRfNew.getId());
                result.setSubjectRf(subjectRfNew);
            }
            if (GSPosNew != null) {
                GSPosNew = em.getReference(GSPosNew.getClass(), GSPosNew.getIdPoselen());
                result.setGSPos(GSPosNew);
            }
            if (rajonNew != null) {
                rajonNew = em.getReference(rajonNew.getClass(), rajonNew.getIdRajon());
                result.setRajon(rajonNew);
            }
            if (dicIstNew != null) {
                dicIstNew = em.getReference(dicIstNew.getClass(), dicIstNew.getIdIst());
                result.setDicIst(dicIstNew);
            }
            if (geoobjectNew != null) {
                geoobjectNew = em.getReference(geoobjectNew.getClass(), geoobjectNew.getIdNp());
                result.setGeoobject(geoobjectNew);
            }
            result = em.merge(result);
            if (dicUnitOld != null && !dicUnitOld.equals(dicUnitNew)) {
                dicUnitOld.getResultSet().remove(result);
                dicUnitOld = em.merge(dicUnitOld);
            }
            if (dicUnitNew != null && !dicUnitNew.equals(dicUnitOld)) {
                dicUnitNew.getResultSet().add(result);
                dicUnitNew = em.merge(dicUnitNew);
            }
            if (pokazatelOld != null && !pokazatelOld.equals(pokazatelNew)) {
                pokazatelOld.getResultSet().remove(result);
                pokazatelOld = em.merge(pokazatelOld);
            }
            if (pokazatelNew != null && !pokazatelNew.equals(pokazatelOld)) {
                pokazatelNew.getResultSet().add(result);
                pokazatelNew = em.merge(pokazatelNew);
            }
            if (dicTimeOld != null && !dicTimeOld.equals(dicTimeNew)) {
                dicTimeOld.getResultSet().remove(result);
                dicTimeOld = em.merge(dicTimeOld);
            }
            if (dicTimeNew != null && !dicTimeNew.equals(dicTimeOld)) {
                dicTimeNew.getResultSet().add(result);
                dicTimeNew = em.merge(dicTimeNew);
            }
            if (selsovetOld != null && !selsovetOld.equals(selsovetNew)) {
                selsovetOld.getResultSet().remove(result);
                selsovetOld = em.merge(selsovetOld);
            }
            if (selsovetNew != null && !selsovetNew.equals(selsovetOld)) {
                selsovetNew.getResultSet().add(result);
                selsovetNew = em.merge(selsovetNew);
            }
            if (rajonOldOld != null && !rajonOldOld.equals(rajonOldNew)) {
                rajonOldOld.getResultSet().remove(result);
                rajonOldOld = em.merge(rajonOldOld);
            }
            if (rajonOldNew != null && !rajonOldNew.equals(rajonOldOld)) {
                rajonOldNew.getResultSet().add(result);
                rajonOldNew = em.merge(rajonOldNew);
            }
            if (subjectRfOld != null && !subjectRfOld.equals(subjectRfNew)) {
                subjectRfOld.getResultSet().remove(result);
                subjectRfOld = em.merge(subjectRfOld);
            }
            if (subjectRfNew != null && !subjectRfNew.equals(subjectRfOld)) {
                subjectRfNew.getResultSet().add(result);
                subjectRfNew = em.merge(subjectRfNew);
            }
            if (GSPosOld != null && !GSPosOld.equals(GSPosNew)) {
                GSPosOld.getResultSet().remove(result);
                GSPosOld = em.merge(GSPosOld);
            }
            if (GSPosNew != null && !GSPosNew.equals(GSPosOld)) {
                GSPosNew.getResultSet().add(result);
                GSPosNew = em.merge(GSPosNew);
            }
            if (rajonOld != null && !rajonOld.equals(rajonNew)) {
                rajonOld.getResultSet().remove(result);
                rajonOld = em.merge(rajonOld);
            }
            if (rajonNew != null && !rajonNew.equals(rajonOld)) {
                rajonNew.getResultSet().add(result);
                rajonNew = em.merge(rajonNew);
            }
            if (dicIstOld != null && !dicIstOld.equals(dicIstNew)) {
                dicIstOld.getResultSet().remove(result);
                dicIstOld = em.merge(dicIstOld);
            }
            if (dicIstNew != null && !dicIstNew.equals(dicIstOld)) {
                dicIstNew.getResultSet().add(result);
                dicIstNew = em.merge(dicIstNew);
            }
            if (geoobjectOld != null && !geoobjectOld.equals(geoobjectNew)) {
                geoobjectOld.getResultSet().remove(result);
                geoobjectOld = em.merge(geoobjectOld);
            }
            if (geoobjectNew != null && !geoobjectNew.equals(geoobjectOld)) {
                geoobjectNew.getResultSet().add(result);
                geoobjectNew = em.merge(geoobjectNew);
            }
            em.getTransaction().commit();
        } catch (Exception ex) {
            String msg = ex.getLocalizedMessage();
            if (msg == null || msg.length() == 0) {
                Integer id = result.getIdRezult();
                if (findResult(id) == null) {
                    throw new NonexistentEntityException("The result with id " + id + " no longer exists.");
                }
            }
            throw ex;
        } finally {
            if (em != null) {
                em.close();
            }
        }
    }

    public void destroy(Integer id) throws NonexistentEntityException {
        EntityManager em = null;
        try {
            em = getEntityManager();
            em.getTransaction().begin();
            Result result;
            try {
                result = em.getReference(Result.class, id);
                result.getIdRezult();
            } catch (EntityNotFoundException enfe) {
                throw new NonexistentEntityException("The result with id " + id + " no longer exists.", enfe);
            }
            DicUnit dicUnit = result.getDicUnit();
            if (dicUnit != null) {
                dicUnit.getResultSet().remove(result);
                dicUnit = em.merge(dicUnit);
            }
            Pokazatel pokazatel = result.getPokazatel();
            if (pokazatel != null) {
                pokazatel.getResultSet().remove(result);
                pokazatel = em.merge(pokazatel);
            }
            DicTime dicTime = result.getDicTime();
            if (dicTime != null) {
                dicTime.getResultSet().remove(result);
                dicTime = em.merge(dicTime);
            }
            Selsovet selsovet = result.getSelsovet();
            if (selsovet != null) {
                selsovet.getResultSet().remove(result);
                selsovet = em.merge(selsovet);
            }
            RajonOld rajonOld = result.getRajonOld();
            if (rajonOld != null) {
                rajonOld.getResultSet().remove(result);
                rajonOld = em.merge(rajonOld);
            }
            SubjectRf subjectRf = result.getSubjectRf();
            if (subjectRf != null) {
                subjectRf.getResultSet().remove(result);
                subjectRf = em.merge(subjectRf);
            }
            GSPos GSPos = result.getGSPos();
            if (GSPos != null) {
                GSPos.getResultSet().remove(result);
                GSPos = em.merge(GSPos);
            }
            Rajon rajon = result.getRajon();
            if (rajon != null) {
                rajon.getResultSet().remove(result);
                rajon = em.merge(rajon);
            }
            DicIst dicIst = result.getDicIst();
            if (dicIst != null) {
                dicIst.getResultSet().remove(result);
                dicIst = em.merge(dicIst);
            }
            Geoobject geoobject = result.getGeoobject();
            if (geoobject != null) {
                geoobject.getResultSet().remove(result);
                geoobject = em.merge(geoobject);
            }
            em.remove(result);
            em.getTransaction().commit();
        } finally {
            if (em != null) {
                em.close();
            }
        }
    }

    public List<Result> findResultEntities() {
        return findResultEntities(true, -1, -1);
    }

    public List<Result> findResultEntities(int maxResults, int firstResult) {
        return findResultEntities(false, maxResults, firstResult);
    }

    private List<Result> findResultEntities(boolean all, int maxResults, int firstResult) {
        EntityManager em = getEntityManager();
        try {
            CriteriaQuery cq = em.getCriteriaBuilder().createQuery();
            cq.select(cq.from(Result.class));
            Query q = em.createQuery(cq);
            if (!all) {
                q.setMaxResults(maxResults);
                q.setFirstResult(firstResult);
            }
            return q.getResultList();
        } finally {
            em.close();
        }
    }

    public Result findResult(Integer id) {
        EntityManager em = getEntityManager();
        try {
            return em.find(Result.class, id);
        } finally {
            em.close();
        }
    }

    public int getResultCount() {
        EntityManager em = getEntityManager();
        try {
            CriteriaQuery cq = em.getCriteriaBuilder().createQuery();
            Root<Result> rt = cq.from(Result.class);
            cq.select(em.getCriteriaBuilder().count(rt));
            Query q = em.createQuery(cq);
            return ((Long) q.getSingleResult()).intValue();
        } finally {
            em.close();
        }
    }

    public Integer getMaxDicTimeByIdP(Integer pokazatel) {
        EntityManager em = getEntityManager();
        Query query = null;
        if (pokazatel != 1) {
            query = em.createNativeQuery("SELECT MAX(ID_TIME) FROM result where id_P=?1");
        } else {
            query = em.createNativeQuery("SELECT MAX(ID_TIME) FROM result where id_P=?1 AND ID_TIME IN "
                    + "(SELECT ID_TIME FROM dic_time WHERE Flag_nas=true)");
        }
        query.setParameter(1, pokazatel);
        List results = query.getResultList();
        return (Integer) results.get(0);
    }

    public String getResByIdPIdGeoobjeIdTime(Integer pokazatel, Integer idGeoobje, Integer dicTime) {

        EntityManager em = getEntityManager();
        Query query = em.createNativeQuery("SELECT RES FROM `result` WHERE ID_GEOOBJE=?1 AND ID_P=?2 AND ID_TIME=?3");
        query.setParameter(1, idGeoobje);
        query.setParameter(2, pokazatel);
        query.setParameter(3, dicTime);
        return (query.getResultList().isEmpty() ? "" : query.getSingleResult().toString().trim());
    }

    public ArrayList<Integer> getIdTimeByPokazatel(Pokazatel... pokazatels) {
        EntityManager em = getEntityManager();
        Query q = null;
        if (pokazatels.length == 1) {
            if (pokazatels[0] instanceof Pokazatel) {
                Pokazatel pokazatel = (Pokazatel) pokazatels[0];
                q = em.createNativeQuery("SELECT ID_TIME FROM result WHERE ID_P=" + pokazatel.getIdP() + " GROUP BY ID_TIME");
            }
        } else if (pokazatels == null || pokazatels.length == 0) {
            return null;
        } else {
            String pokazatelsConcat = "";
            for (int i = 0; i < pokazatels.length; i++) {
                Pokazatel pokazatel = pokazatels[i];
                if (i == 0) {
                    pokazatelsConcat = "ID_P=" + pokazatel.getIdP().toString();
                } else {
                    pokazatelsConcat += " OR ID_P=" + pokazatel.getIdP().toString();
                }
            }
            q = em.createNativeQuery("SELECT ID_TIME FROM result WHERE " + pokazatelsConcat + " GROUP BY ID_TIME");
        }
        return new ArrayList<Integer>(q.getResultList());
    }

    public String getComment(Integer idTime, Integer idGeoobje, Integer idP) {
        EntityManager em = getEntityManager();
        Query q = em.createNativeQuery("SELECT NAME FROM dic_ist WHERE ID_IST=(SELECT ID_IST FROM result WHERE ID_GEOOBJE = " + idGeoobje + " AND ID_P = " + idP + " AND ID_TIME = " + idTime + ")");
        if (q.getResultList().isEmpty()) {
            return "";
        } else {
            if (q.getResultList().get(0) instanceof String) {
                String string = (String) q.getResultList().get(0);
                return string;
            } else {
                return "";
            }
        }
    }

    public ArrayList<Integer> getGeoobjectsByZone(int zoneId, List<Integer> exclude, PokazatelMap... pms) {
        String list = "";
        if (exclude != null) {
            for (int i = 0; i < exclude.size(); i++) {
                Integer id = exclude.get(i);
                if (i == 0) {
                    list += id;
                } else {
                    list += "," + id;
                }
            }
        }
        EntityManager em = getEntityManager();
        Query q;
        switch (zoneId) {
            case 1:
                q = em.createNativeQuery("SELECT ID_GEOOBJE FROM geoobj.result WHERE REPLACE(RES,'<','')>40 AND ID_P=?1 AND ID_TIME=?2  AND ID_GEOOBJE  IN (SELECT ID_NP FROM geoobj.cross_zon WHERE DATE_ZONN IS NULL AND ID_ZON=1)");
                q.setParameter(1, pms[0].getPokazatel().getIdP());
                q.setParameter(2, pms[0].getTime().getIdTime());
                break;
            case 2:
                q = em.createNativeQuery("SELECT ID_GEOOBJE FROM geoobj.`result` WHERE ID_GEOOBJE NOT IN (?1) AND ((REPLACE(RES,'<','')>5 AND ID_P=?2 AND ID_TIME=?3) OR (REPLACE(RES,'<','')>15 AND ID_P=?4 AND ID_TIME=?5)) GROUP BY ID_GEOOBJE ORDER BY ID_SUBJECT, ID_RAJON, ID_RAJON_O, ID_POSELEN, ID_SELSOV");
                q.setParameter(2, pms[0].getPokazatel().getIdP());
                q.setParameter(3, pms[0].getTime().getIdTime());
                q.setParameter(4, pms[1].getPokazatel().getIdP());
                q.setParameter(5, pms[1].getTime().getIdTime());
                q.setParameter(1, list);
                break;
            case 3:
                q = em.createNativeQuery("SELECT ID_GEOOBJE FROM geoobj.`result` WHERE ID_GEOOBJE NOT IN (?1) AND ((REPLACE(RES,'<','')>1 AND ID_P=?2 AND ID_TIME=?3) OR (REPLACE(RES,'<','')>=5 AND REPLACE(RES,'<','')<=15 AND ID_P=?4 AND ID_TIME=?5)) GROUP BY ID_GEOOBJE ORDER BY ID_SUBJECT, ID_RAJON, ID_RAJON_O, ID_POSELEN, ID_SELSOV");
                q.setParameter(2, pms[0].getPokazatel().getIdP());
                q.setParameter(3, pms[0].getTime().getIdTime());
                q.setParameter(4, pms[1].getPokazatel().getIdP());
                q.setParameter(5, pms[1].getTime().getIdTime());
                q.setParameter(1, list);
                break;
            case 4:
                q = em.createNativeQuery("SELECT ID_GEOOBJE FROM geoobj.`result` WHERE ID_GEOOBJE NOT IN (?1) AND ID_P=?2 AND ID_TIME=?3 AND REPLACE(RES,'<','')>1 AND REPLACE(RES,'<','')<5 GROUP BY ID_GEOOBJE ORDER BY ID_SUBJECT, ID_RAJON, ID_RAJON_O, ID_POSELEN, ID_SELSOV");
                q.setParameter(2, pms[1].getPokazatel().getIdP());
                q.setParameter(3, pms[1].getTime().getIdTime());
                q.setParameter(1, list);
                break;
            default:
                return null;
        }

        if (q.getResultList().isEmpty()) {
            return null;
        } else {
            List<Integer> asd = q.getResultList();
            ArrayList<Integer> finalResult = new ArrayList<Integer>();
            for (Integer integer : asd) {
                if (!exclude.contains(integer)) {
                    finalResult.add(integer);
                }
            }
            return finalResult;
        }
    }
}
